<?php
class Admin_Model_Category extends Application_Model_Db_Db
{    

    protected $_availableForPostTypeIds = array(
        2,
        3
    );

    protected $_availableForCategoryTypeIds = array(
        1,
        5
    );

    /** 
     * Select categories availables for current user
     * and which can contains posts.     
     * 
     * @param string $availableCategories     
     * @return array
     */
    public function getAvailableForPostCategories($availableCategories, $canHaveChildren=false) {        
        $sql = "SELECT 
                    `category`.`id`,
                    `category`.`category_name`,
                    `category`.`parent_id`,
                    `parent`.`category_name` AS `parent_name`
                FROM `category`                
                LEFT JOIN (
                    SELECT 
                        `id`, 
                        `category_name`
                    FROM `category`
                ) AS `parent`
                    ON `category`.`parent_id` = `parent`.`id`
                WHERE 
                    1
                    AND `category`.`id` IN ({$availableCategories})
                    AND `category`.`is_link` = '0'";        
        return $this->_db->fetchAll($sql);
    }
    
    /**
     * Select categories available for current user
     * If {$canHaveChildren} is true - getting only the categories,
     * that can have children categories
     * @param int $roleId
     * @param bool $canHaveChildren
     * @return array     
     */
    public function getAvailableForRoleCategories($roleId, $canHaveChildren=false) {
        $where = '';
        if ($canHaveChildren) 
            $where .= " AND `type`.`can_have_children` = '1'";        
        
        $sql = "SELECT 
                    `category`.`id`,
                    `category`.`category_name`,
                    `category`.`parent_id`,
                    IFNULL(`parent`.`category_name`, 'Отсутствует') AS `parent_name`,
                    IFNULL(`department`.`name`, 'Отсутствует') AS `department_name`
                FROM `category`
                INNER JOIN `category_type` AS `type`
                    ON `category`.`type_id` = `type`.`id`
                INNER JOIN `role_categories`
                    ON `category`.`id` = `role_categories`.`category_id`
                LEFT JOIN `category` AS `parent`
                    ON `category`.`parent_id` = `parent`.`id`
                LEFT JOIN `department`
                    ON `category`.`department_id` = `department`.`id`                
                WHERE 
                    1
                    AND `role_categories`.`role_id` = ?
                    {$where}
                ORDER BY `category`.`parent_id`";
        return $this->_db->fetchAll($sql, $roleId);
    }

    /**
     * Select categories by department id 
     * and available for current user     
     * @param int $roleId
     * @param int $departmentId
     * @return array     
     */
    public function getAvailableForRoleCategoriesByDepartmentId($roleId, $departmentId=0) {
        $sql = "SELECT 
                    `category`.`id`,
                    `category`.`category_name`,
                    `category`.`parent_id`,
                    IFNULL(`parent`.`category_name`, 'Отсутствует') AS `parent_name`
                FROM `category`
                INNER JOIN `category_type` AS `type`
                    ON `category`.`type_id` = `type`.`id`
                INNER JOIN `role_categories`
                    ON `category`.`id` = `role_categories`.`category_id`
                LEFT JOIN `category` AS `parent`
                    ON `category`.`parent_id` = `parent`.`id`                
                WHERE 
                    1
                    AND `role_categories`.`role_id` = ?
                    AND `category`.`department_id` = ?
                ORDER BY `category`.`parent_id`";
        return $this->_db->fetchAll($sql, array($roleId, $departmentId));
    }

    public function getCategoriesByDepartmentId($departmentId, $roleCategories) {
        $where = '';
        if ($roleCategories) {
            $where = " AND `category`.`id` NOT IN ({$roleCategories})";
        }
        $sql = "SELECT 
                    `category`.`id`,
                    `category`.`category_name`,
                    `category`.`parent_id`,
                    IFNULL(`parent`.`category_name`, 'Отсутствует') AS `parent_name`
                FROM `category`
                LEFT JOIN `category` AS `parent`
                    ON `category`.`parent_id` = `parent`.`id`
                WHERE 
                    1
                    AND `category`.`department_id` = ?
                    {$where}";
        return $this->_db->fetchAll($sql, $departmentId);                     
    }

    /**
     * Select categories available for current user by parent id
     *      
     * @param int $roleId
     * @param int $parentId
     * @return array     
     */
    public function getAvailableForRoleCategoriesByParentId($roleId, $parentId=0) {        
        $sql = "SELECT 
                    `category`.`id`,
                    `category`.`category_name`,
                    `category`.`parent_id`,
                    IFNULL(`parent`.`category_name`, 'Отсутствует') AS `parent_name`
                FROM `category`
                INNER JOIN `category_type` AS `type`
                    ON `category`.`type_id` = `type`.`id`
                INNER JOIN `role_categories`
                    ON `category`.`id` = `role_categories`.`category_id`
                LEFT JOIN `category` AS `parent`
                    ON `category`.`parent_id` = `parent`.`id`
                WHERE 
                    1
                    AND `role_categories`.`role_id` = ?
                    AND `category`.`parent_id` = ?
                ORDER BY `category`.`position`";
        return $this->_db->fetchAll($sql, array($roleId, $parentId));
    }

    public function getParentCategoryBySubCategoryId($categoryId) {
        $sql = "SELECT 
                    `parent_category`.`id`,
                    `parent_category`.`category_name`,
                    `parent_category`.`parent_id`,
                    IFNULL(`parent_parent_category`.`category_name`, 'Отсутствует') AS `parent_name`
                FROM `category`
                INNER JOIN `category` AS `parent_category`
                    ON `category`.`parent_id` = `parent_category`.`id`
                LEFT JOIN `category` AS `parent_parent_category`
                    ON `parent_category`.`parent_id` = `parent_parent_category`.`id`
                WHERE 
                    1
                    AND `category`.`id` = ?
                ORDER BY `category`.`position`";
        return $this->_db->fetchRow($sql, intval($categoryId));
    }

    public function canBeParentFor($potentialSubId, $potentialParentId) {
        if ($potentialSubId == $potentialParentId) {
            return false;
        } elseif ($potentialParentId == 0) {
            return true;
        }
        $sql = "SELECT
                    `category`.`id`,
                    `category`.`parent_id`
                FROM `category`
                WHERE `category`.`id` = ?";        
        $result = $this->_db->fetchRow($sql, $potentialParentId);        
        return $this->canBeParentFor($potentialSubId, $result['parent_id']);        
    }
    
    /**
     * Select category types availables for current user
     * and which contain one or more posts
     * 
     * @param string $availableCategories
     * @return array
     */ 
    public function getAvailableAndNotEmptyCategoryTypes($availableCategories) {
        $sql = "SELECT
                    `type`.`type`,
                    COUNT(`content`.`id`) AS `count`
                FROM `content`
                INNER JOIN `category`
                    ON `content`.`category_id` = `category`.`id`
                INNER JOIN `category_type` AS `type`
                    ON `category`.`type_id` = `type`.`id`
                WHERE 
                    1
                    AND `category`.`id` IN ({$availableCategories})
                    AND `category`.`is_link` = '0'
                GROUP BY `type`.`type`
                ORDER BY NULL";
        return $this->_db->fetchAll($sql);
    }

    public function getAvailableForRoleCategoryTypes($roleId) {
        $sql = "SELECT 
                    `type`.`id`,
                    `type`.`type`
                FROM `category_type` AS `type`
                INNER JOIN `role_category_types` AS `role_types`
                    ON `type`.`id` = `role_types`.`category_type_id`
                WHERE `role_types`.`role_id` = {$roleId}";
        return $this->_db->fetchAll($sql);
    }

    /**
     * Add new category
     * @param array $data
     * @return int od of the stored category
     */
    public function addCategory($data) {
        $this->_db->insert('category', $data);
        return $this->_db->lastInsertId();
    }
    
    /**
     * Allow access for role for current category
     * @param int $categoryId
     * @param int $roleId
     * @return void
     */ 
    public function allowCategoryForRole($categoryId, $roleId) {
        $this->_db->insert(
            'role_categories', 
            array(
                'role_id'     => $roleId,
                'category_id' => $categoryId
            )
        );
    }

    /**
     * get category department id by category id
     * @param int $categoryId
     * @return int
     */
    public function getCategoryDepartmentId($categoryId) {
        $sql = "SELECT
                    `category`.`department_id`
                FROM `category`
                WHERE `category`.`id` = ?";
        return $this->_db->fetchOne($sql, $categoryId);
    }

    public function validate($data, $currentId=0, $deptId=0) {
        if (empty($data['category_name'])) {
            $data['error'] = 'Category name cannot be empty';
            return $data;
        }  

        if (isset($data['alias']))
            $data['alias'] = ltrim(rtrim($data['alias']));        
        
        if (isset($data['alias'])) {            
            if (empty($data['alias'])) {            
                $data['alias'] = implode('-', explode(' ', mb_strtolower($data['category_name'], 'UTF-8')));
            } else {
                $data['alias'] = implode('-', explode(' ', mb_strtolower($data['alias'], 'UTF-8')));                
            }
            //alias must be unique
            if ($this->_aliasIsExist($data['alias'], $currentId, $deptId)) {
                $errors['error'] = 'alias already exists';
                return $errors;
            } else {
                $regEx = '/^[a-zA-Z\p{Cyrillic}0-9().\s\-]+$/u';                        
                if (!preg_match($regEx, $data['alias'])) {
                    $errors['error'] = 'alias bad format';
                    return $errors;
                }
            }                                                               
        }  
        $data['alias'] = str_replace('’', '', $data['alias']);
        return $data;
    }

    /**
     * Return position of the last available category
     * @param string $availableCategories
     * @param int $parentCategoryId
     * @return int max postion
     */ 
    public function getMaxCategoryPosition($availableCategories, $parentCategoryId) {
        $sql = "SELECT 
                    MAX(`position`)
                FROM `category`
                WHERE 
                    1
                    AND `parent_id` = {$parentCategoryId}
                    AND `id` IN ({$availableCategories})";
        return $this->_db->fetchOne($sql);
    }

    /**
     * Delete category
     * @param int $categoryId
     * @return void
     */ 
    public function deleteCategory($categoryId) {
        $where = 'id = ' . intval($categoryId);
        $this->_db->delete('category', $where);
        $this->resetDeleteCategoryPostsCategoryId($categoryId);
    }

    /**
     * Reset category id of the deleted category posts
     * @param int $categoryId
     * @return void
     */
    public function resetDeleteCategoryPostsCategoryId($categoryId) {
        $data = array('category_id' => 0);
        $where = 'category_id = ' . intval($categoryId);        
        $this->_db->update('content', $data, $where);
    }

    /**
     * Get selected category data
     * @param int $categoryId
     * @return array     
     */
    public function getCategoryData($categoryId) {
        $sql = "SELECT                     
                    `category`.`parent_id`,
                    `category`.`type_id`,
                    `category`.`description`,
                    `category`.`keywords`,
                    `category`.`category_name`,
                    `category`.`alias`,
                    `category`.`menu`,
                    `category`.`enabled`,
                    `category`.`is_link`
                FROM `category`
                WHERE `category`.`id` = ?";
        return $this->_db->fetchRow($sql, $categoryId);
    }

    /**
     * Update selected category
     * @param array $data
     * @param int $categoryId
     * @return void     
     */
    public function updateCategory($data, $categoryId) {
        $where = 'id = ' . intval($categoryId);
        $this->_db->update('category', $data, $where);
    }

    /**
     * Recursively update all categories position from data
     * @param $data
     */
    public function updateCategoriesPosition($data) {        
        $position = 1;
        foreach ($data as $category) {  
            if ($category['disable'] == 0) {
                $this->_db->update(
                    'category',
                    array(
                        'position' => $position
                    ),
                    'id = ' . $category['id']
                );
            }                       
            if (isset($category['children'])) {
                $this->updateCategoriesPosition($category['children']);
            }
            $position++;
        }
    }

    public function getAllDepartmentCategories($departmentId=0) {
        $sql = "SELECT
                    `category`.`id`,
                    `category`.`category_name`,
                    IFNULL(`parent`.`category_name`, '') AS `parent_category_name`                
                FROM `category`
                LEFT JOIN `category` AS `parent`
                    ON `category`.`parent_id` = `parent`.`id`
                WHERE `category`.`department_id` = ?
                ";
        return $this->_db->fetchAll($sql, $departmentId);
    }

    public function getAllCategoryTypes() {
        $sql = "SELECT
                    `id`,
                    `type`
                FROM `category_type`";
        return $this->_db->fetchAll($sql);
    }

    public function getAllCategoryTypesExcept($categoryTypes) {
        $where = '';
        if ($categoryTypes) {
            $where = " WHERE `id` NOT IN ({$categoryTypes})";
        }
        $sql = "SELECT
                    `id`,
                    `type`
                FROM `category_type`
                {$where}";
        return $this->_db->fetchAll($sql);
    }

    public function checkAvailabilityCategory($roleId, $categoryId) {
        $sql = "SELECT
                    `category`.`parent_id`
                FROM `category`
                INNER JOIN `role_categories`
                    ON `category`.`id` = `role_categories`.`category_id`
                WHERE 
                    1
                    AND `role_categories`.`role_id` = ?
                    AND `role_categories`.`category_id` = ? ";
        return $this->_db->fetchOne($sql, array($roleId, $categoryId));        
    }

    public function getAllCategoriesWhereIdIn($categoriesIds) {
        $where = '';
        if ($categoriesIds)
            $where = "WHERE `id` IN ({$categoriesIds})";

        $sql = "SELECT
                    `id`,
                    `parent_id`,
                    `category_name`
                FROM `category`
                {$where}
                ORDER BY `position`";
        return $this->_db->fetchAll($sql);
    }

    public function getTotalCategoryCountsByParentId($parentId) {
        $sql = "SELECT
                    COUNT(`id`)
                FROM `category`
                WHERE `parent_id` = ?";
        return $this->_db->fetchOne($sql, $parentId);
    }

    public function checkCategoryType($typeId, $post=true) {
        if ($post)
            $ids = $this->_availableForPostTypeIds;
        else
            $ids = $this->_availableForCategoryTypeIds;
        
        if (in_array($typeId, $ids)) {
            return true;
        }
        return false;
    }    

    /**
     * Check for the existence of an alias
     * 
     * @param string $alias
     * @param int $currentId
     * @return int id of an alias 
     */
    protected function _aliasIsExist($alias, $currentId, $deptId) {
        $sql = "SELECT
                   `id`
                FROM `category`
                WHERE 
                    1
                    AND `alias` = ?
                    AND `id` != ?
                    AND `department_id` = ?";
        return $this->_db->fetchOne($sql, array($alias, $currentId, $deptId));
    }        
}